<template>
    <el-upload class="upload-demo" drag accept=".xlsx" :auto-upload="false" @change="handleFileUpload">
        <el-icon class="el-icon--upload"><upload-filled /></el-icon>
        <div class="el-upload__text">
            拖动文件到这里或者 <em>点击上传</em>
        </div>
    </el-upload>

</template>
<script setup>
import * as XLSX from 'xlsx';
import { defineEmits, onMounted, provide, reactive } from 'vue';
import { Achievement_Of_Course_Objectives } from '@/api/paper'
import { UploadFilled } from '@element-plus/icons-vue'
import { getPerformance_cols } from '@/api/target_item_info_input'
import eventBus from '/eventBus'
import { useRoute } from 'vue-router';
const route = useRoute();
const subId = parseInt(route.params.subId);
import { useClassStore } from '@/stores/modules/class'
const classStore = useClassStore()
const acs_id = classStore.classInfo.user_and_acs_id
let performance_Homework_Cols
onMounted(async()=>{
    const res = await getPerformance_cols(acs_id)
    performance_Homework_Cols=res.data.targetNum
})
const handleFileUpload = async (file, fileList) => {
    // const file = event.target.files[0];
    // 你不需要通过event来获取文件，fileList参数包含了所有文件的信息
    const rawFile = fileList[fileList.length - 1].raw; // 获取最新添加的文件对象
    const reader = new FileReader();
    reader.onload = (e) => {
        const workbook = XLSX.read(e.target.result, { type: 'binary' });
        const sheetName = workbook.SheetNames[0];
        const sheet = workbook.Sheets[sheetName];
        const range = XLSX.utils.decode_range(sheet['!ref']);
        // calculateTotalScoresFOR_performance_and_homework(sheet, range);
        calculateTotalScoresFOR_performance_and_homework(sheet, range, performance_Homework_Cols);
        // console.log("objectiveScores handle", objectiveScores);
        computeStatistics_FOR_OBJ_SCORES(sheet, range)
        computeAndCategorizeTotalScores(sheet, range, performance_Homework_Cols)
        const statistics = computeStatistics_FOR_INSERT(sheet, range);
        // eventBus.methods.setExtraTable(statistics)
        insertStatisticsIntoSheet(sheet, range, statistics, performance_Homework_Cols);
        exportModifiedExcel(workbook, 'updated_file.xlsx', statistics);
    };
    reader.readAsBinaryString(rawFile);
};
let objectiveScores
const calculateTotalScoresFOR_performance_and_homework = (sheet, range, performance_Homework_Cols) => {
    console.log("performance_Homework_Cols",performance_Homework_Cols);
    const PERFORMANCE_COLS = performance_Homework_Cols; // 课堂表现列数
    const HOMEWORK_COLS = performance_Homework_Cols;    // 课堂作业列数
    const START_ROW = 3;        // 数据起始行
    const START_COL = 2;        // 数据起始列

    // 初始化对象数组，每个元素代表一个课程目标，包含表现分数和作业分数
    objectiveScores = Array.from({ length: PERFORMANCE_COLS }, () => ({
        performance: 0,
        homework: 0
    }));

    for (let R = START_ROW; R <= range.e.r; R++) {
        // 处理课堂表现分数
        for (let C = START_COL, objectiveIndex = 0; C < START_COL + PERFORMANCE_COLS; C++, objectiveIndex++) {
            const cellRef = XLSX.utils.encode_cell({ c: C, r: R });
            const cellValue = sheet[cellRef] ? sheet[cellRef].v : 0;
            objectiveScores[objectiveIndex].performance += cellValue;
        }

        // 处理课堂作业分数
        for (let C = START_COL + PERFORMANCE_COLS, objectiveIndex = 0; C < START_COL + PERFORMANCE_COLS + HOMEWORK_COLS; C++, objectiveIndex++) {
            const cellRef = XLSX.utils.encode_cell({ c: C, r: R });
            const cellValue = sheet[cellRef] ? sheet[cellRef].v : 0;
            objectiveScores[objectiveIndex].homework += cellValue;
        }
    }

    // 打印出每个课程目标的总分
    console.log("Objective Scores:", objectiveScores);
};



const emits = defineEmits(['updateData']);
const exportModifiedExcel = (workbook, fileName, extraTableData) => {
    XLSX.writeFile(workbook, fileName);
    // 将 workbook 对象转换为 JSON 格式并发射事件
    const sheetName = workbook.SheetNames[0];
    const sheet = workbook.Sheets[sheetName];
    const json = XLSX.utils.sheet_to_json(sheet, { header: 1 });
    const temData=[]
    Object.entries(extraTableData).forEach(([key, value]) => {
        value.forEach(item => {
            temData.push(item)
        })
    });
    // console.log("temData123123123", temData);
    const avgValues = temData.map(obj => obj.avg);
    const maxValues = temData.map(obj => obj.max);
    const minValues = temData.map(obj => obj.min);
    const varianceValues = temData.map(obj => obj.variance);

    const mergedData = reactive({
        '平均值': avgValues,
        '最大值': maxValues,
        '最小值': minValues,
        '方差': varianceValues
    });
    emits('updateData', json, mergedData);  // 触发 updateData 事件，将数据传给父组件
};
// 计算每个学生的总分
const computeAndCategorizeTotalScores = (sheet, range, performance_Homework_Cols) => {
    const SCORE_START_COL = performance_Homework_Cols*2+2; //
    const SCORE_END_COL = range.e.c;  // 
    const SCORE_START_ROW = 3; // 学生的分数从第四行开始
    let totalScores = [];
    let categorizedScores = {
        above90: [],
        between60and90: [],
        below60: []
    };
    // 计算每个学生的总分
    for (let R = SCORE_START_ROW; R <= range.e.r; R++) {
        let totalScore = 0;
        for (let C = SCORE_START_COL; C <= SCORE_END_COL; C++) {
            const scoreCellRef = XLSX.utils.encode_cell({ c: C, r: R });
            const scoreCell = sheet[scoreCellRef];
            if (scoreCell && !isNaN(scoreCell.v)) {
                totalScore += scoreCell.v;
            }
        }
        totalScores.push(totalScore);
        // 分类计算总分
        if (totalScore >= 90) {
            categorizedScores.above90.push(totalScore);
        } else if (totalScore >= 60 && totalScore < 90) {
            categorizedScores.between60and90.push(totalScore);
        } else {
            categorizedScores.below60.push(totalScore);
        }
    }
    eventBus.methods.setScores([
        { name: '90分以上', value: categorizedScores.above90.length },
        { name: '60-90分之间', value: categorizedScores.between60and90.length },
        { name: '60分以下', value: categorizedScores.below60.length },
    ])
};

let studentCount = 0;
//  在Excel的最右侧添加每个学生的课程目标得分
const insertStudentScores = (sheet, range, studentScores) => {
    const insertAtColStart = range.e.c + 1; // 新列的起始位置
    let insertAtCol = insertAtColStart;
    const TYPE_ROW = 1; // 课程目标所在的行
    const SCORE_START_ROW = 3; // 分数开始的行

    Object.keys(studentScores[SCORE_START_ROW]).forEach((objective, index) => {
        // 对每个课程目标，添加新列并填充学生得分
        const headerCellRef = XLSX.utils.encode_cell({ c: insertAtCol, r: TYPE_ROW + 1 });
        sheet[headerCellRef] = { t: 's', v: `${objective}得分` }; // 设置列标题

        for (let R = SCORE_START_ROW; R <= range.e.r; ++R) {
            const scoreCellRef = XLSX.utils.encode_cell({ c: insertAtCol, r: R });
            const score = studentScores[R] ? studentScores[R][objective] : 0;
            sheet[scoreCellRef] = { t: 'n', v: score };
        }

        insertAtCol++;
    });

    // 更新表格范围以包含新添加的列
    range.e.c = insertAtCol - 1;
    sheet['!ref'] = XLSX.utils.encode_range(range);
}
// 用于计算各个课程目标平均分（如：全部课程目标1的分数/学生数）
const computeStatistics_FOR_OBJ_SCORES = async (sheet, range) => {
    const TYPE_ROW = 1; // 课程目标所在的行
    const SCORE_START_ROW = 3; // 分数开始的行
    let courseObjectivesTotal = {}; // 累加每个课程目标的总分
    let studentScoresForCourseObjectives = {}; // 每个学生在各个课程目标上的得分
    // 学生人数
    let currentType = null;
    let typeStatistics = []
    studentCount = range.e.r - SCORE_START_ROW + 1; // 计算学生人数

    for (let C = range.s.c; C <= range.e.c; ++C) {
        const typeCellRef = XLSX.utils.encode_cell({ c: C, r: TYPE_ROW });
        const typeCell = sheet[typeCellRef];
        if (typeCell && typeCell.v) {
            const objectiveMatch = typeCell.v.match(/课程目标(\d+)/);
            if (objectiveMatch) {
                currentType = `课程目标${objectiveMatch[1]}`;
                courseObjectivesTotal[currentType] = courseObjectivesTotal[currentType] || 0;
            } else {
                currentType = null;
            }
        }

        if (currentType) {
            for (let R = SCORE_START_ROW; R <= range.e.r; ++R) {
                const scoreCellRef = XLSX.utils.encode_cell({ c: C, r: R });
                const scoreCell = sheet[scoreCellRef];
                if (scoreCell && !isNaN(scoreCell.v)) {
                    courseObjectivesTotal[currentType] += scoreCell.v;
                    studentScoresForCourseObjectives[R] = studentScoresForCourseObjectives[R] || {};
                    studentScoresForCourseObjectives[R][currentType] = (studentScoresForCourseObjectives[R][currentType] || 0) + scoreCell.v;
                }
            }
        }
    }
    // 这里执行提交课程目标平均分的逻辑
    // 打印每个课程目标的总分
    // console.log("studentCount", studentCount);
    // console.log("每个课程目标的总分：");
    // console.log("Object.entries(courseObjectivesTotal)", Object.entries(courseObjectivesTotal));
    let DataToSubmit = []
    let cnt=0
    for (const [key, value] of Object.entries(courseObjectivesTotal)) {
        // console.log(key, value);
        const average = value / studentCount;
        // console.log("objectiveScores cnt", objectiveScores, cnt);
        DataToSubmit.push({
            type: key,
            score: average.toFixed(2),
            taskScore: objectiveScores[cnt].homework,
            courseScore: objectiveScores[cnt++].performance
        });
        // objectiveScores
        // console.log(`${key}: ${average.toFixed(2)}`); // 保留两位小数
        // console.log(`${key}: ${value}`); // 保留两位小数
    }
    // 将每个学生的课程目标得分添加到Excel的最右侧
    // insertStudentScores(sheet, range, studentScoresForCourseObjectives);
    eventBus.methods.setDataToSubmit(DataToSubmit)
    eventBus.methods.setstudentCount(studentCount)
    await Achievement_Of_Course_Objectives(subId, {
        DataToSubmit,
        studentCount,
        // totalHomework: addtionalInfo.totalHomework,
        // totalPerformance: addtionalInfo.totalPerformance
    })
    return typeStatistics;
};


const QUESTION_ROW = 2;
//计算平均分 最大最小值 方差到的函数（不是插入，实际插入是insertStatisticsIntoSheet函数）
const computeStatistics_FOR_INSERT = (sheet, range) => {
    const TYPE_ROW = 0;
    const SCORE_START_ROW = 3;
    let typeStatistics = {};
    let currentType = null;
    let averageScores = [];
    let TopVarianceQuestions_10 = []
    for (let C = range.s.c; C <= range.e.c; ++C) {
        const typeCellRef = XLSX.utils.encode_cell({ c: C, r: TYPE_ROW });
        const typeCell = sheet[typeCellRef];
        if (typeCell && typeCell.v) {
            currentType = typeCell.v;
            typeStatistics[currentType] = typeStatistics[currentType] || [];
        }

        if (currentType) {
            const questionCellRef = XLSX.utils.encode_cell({ c: C, r: QUESTION_ROW });
            const questionCell = sheet[questionCellRef];
            if (questionCell && typeof questionCell.v === 'number') {
                const scores = [];
                for (let R = SCORE_START_ROW; R <= range.e.r; ++R) {
                    const scoreCellRef = XLSX.utils.encode_cell({ c: C, r: R });
                    const scoreCell = sheet[scoreCellRef];
                    if (scoreCell && !isNaN(scoreCell.v)) {
                        scores.push(scoreCell.v);
                    }
                }
                const sum = scores.reduce((a, b) => a + b, 0);
                // const avg = sum / scores.length;
                const avg = parseFloat((sum / scores.length).toFixed(2));
                averageScores.push({ question: questionCell.v, avg: avg.toFixed(2) });
                typeStatistics[currentType].push({
                    questionNum: questionCell.v,
                    avg: avg.toFixed(2),
                    max: Math.max(...scores).toFixed(2),
                    min: Math.min(...scores).toFixed(2),
                    // variance: scores.reduce((acc, score) => acc + Math.pow(score - avg, 2), 0) / scores.length,
                    variance: (scores.reduce((acc, score) => acc + Math.pow(score - avg, 2), 0) / scores.length).toFixed(2),
                    scores: scores
                });
                TopVarianceQuestions_10.push({
                    question: questionCell.v,
                    variance: parseFloat((scores.reduce((acc, score) => acc + Math.pow(score - avg, 2), 0) / scores.length).toFixed(2))
                }
                   
                )
            }
        }
    }
    // 更新事件总线的平均分数据
    eventBus.methods.setAverageScores(averageScores);

    for (const typeName in typeStatistics) {
        typeStatistics[typeName].sort((a, b) => a.questionNum - b.questionNum);
    }
    TopVarianceQuestions_10=TopVarianceQuestions_10.filter(function (element) {
        return element.variance !== 0.0;
    });

    TopVarianceQuestions_10=TopVarianceQuestions_10.sort((a, b) => b.variance - a.variance)
    TopVarianceQuestions_10 = TopVarianceQuestions_10.slice(0, 10).reverse();
    eventBus.methods.setTop_variance(TopVarianceQuestions_10);
    console.log("typeStatistics", typeStatistics);
    return typeStatistics;
};
//插入平均分 最大最小值 方差到最下方的函数
const insertStatisticsIntoSheet = (sheet, range, statistics, performance_Homework_Cols) => {
    const insertAtRow = range.e.r + 1; // 统计数据插入的起始行位置
    let insertAtCol = range.s.c + (performance_Homework_Cols*2+1); // 统计数据插入的起始列位置
    // 在统计数据的最前面插入一次名称
    sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow })] = { t: 's', v: '平均值' };
    sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow + 1 })] = { t: 's', v: '最大值' };
    sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow + 2 })] = { t: 's', v: '最低值' };
    sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow + 3 })] = { t: 's', v: '方差' };
    insertAtCol++; // 更新插入列位置以反映新添加的名称列
    for (const typeName in statistics) {
        const statsArray = statistics[typeName];
        // 对于每个类型，插入统计数据
        for (let i = 0; i < statsArray.length; i++) {
            const { avg, max, min, variance } = statsArray[i];
            // 插入平均分、最高分、最低分、方差
            sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow })] = { t: 'n', v: avg, z: '0.00' };
            sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow + 1 })] = { t: 'n', v: max, z: '0.00' };
            sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow + 2 })] = { t: 'n', v: min, z: '0.00' };
            sheet[XLSX.utils.encode_cell({ c: insertAtCol, r: insertAtRow + 3 })] = { t: 'n', v: variance, z: '0.00' };

            insertAtCol++; // 更新插入列位置
        }
    }
    // 更新整个工作表的范围
    range.e.r = insertAtRow + 3; // 插入了4行数据，所以+3
    sheet['!ref'] = XLSX.utils.encode_range(range);
};

</script>

<style scoped lang="scss">
.upload-demo{
    height: 200px;
}
</style>
